MySQL クエリチューニング 内部一時テーブルを利用するクエリは要チェック
クエリが内部一時テーブルを作成するかどうかの判断方法
EXPLAINコマンドを利用する（ex: mysql> EXPLAIN SELECT * FROM tbl1;）
1. この結果から、Extra列に「Using temporary」とあるクエリは、内部一時テーブルが生成される可能性が高い。
2. select_type列のDERIVED、SUBQUERY、MATERIALIZEも内部一時テーブルが生成されうる。
内部一時テーブル作成がディスク上で行われてないか確認
Created_tmp_disk_tablesの数が1以上なら、ディスク上でも作成されてるので注意。
code: sample.sql
root@localhost db01 13:39: > show global status like 'Created_tmp%tables'; +-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 50 |
+-------------------------+-------+
ネック解消のための対応策
mysqlのバージョンで対応策が異なる
5.7の場合
内部一時テーブルには、Memoryストレージエンジンが利用される。
そして、以下の最大サイズよりも大きい内部一時テーブルが作成される場合、ディスク上のテーブルに自動的に変換される。
インメモリ内部一時テーブルの最大サイズは....MIN（tmp_table_size, max_heap_table_size）。
8.0の場合
ややこしい...TempTableってのを内部一時テーブルに利用してるのだが、こいつの動きが変数によって変わる変わる。
まずインメモリに置く内部一時テーブルの最大サイズはtemptable_max_ramで決まる。
もしtemptable_use_mmap=ONの場合は、temptable_max_ramの制限を超えたデータに対して、ディスクではなくメモリマップされたファイルを利用するらしい。
要するに、もしこの値がONになってたら、メモリとディスク？の両方で内部一時テーブルのデータを管理するようになるってこと。
詳しくは公式ページ読め。以下も参考になる。
temptable_max_mmapは、メモリマップされたファイルから借り出せるデータサイズっぽい。
もし0なら、メモリにあるデータもろとも、問答無用でディスク送りにされる。
awsのブログも参考になるよ
ちなみに、Auroraのレプリカで内部一時テーブルの限界までいくようなクエリ実行すると、エラー起きる可能性があるらしいwwww
Auroraの特性上仕方なし...K